package com.apes.fico.finance.respository;

import com.apes.fico.finance.model.costcollect.StoreTasks;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;


public interface StoreTasksRepository extends CustomRepository<StoreTasks, String> {

    @Query(value = "SELECT * FROM GL_STORE_TASKS A " +
            "WHERE EXTRACT(MONTH FROM A.YEAR_MONTH) = :months " +
            "AND EXTRACT(YEAR FROM A.YEAR_MONTH) = :years " +
            "AND (A.BUSINESS_TASKS_AMT <> 0 OR A.GROSS_PROFIT_TASKS_AMT <> 0) " +
            "AND A.DEPT_ID = :deptId",nativeQuery = true)
    StoreTasks findByYearMonthAndDept(@Param("months") String months, @Param("years") String years, @Param("deptId") String deptId);

    @Query(value = "SELECT SUM(T.YYE)YYE, SUM(T.MLE)MLE " +
            "                  FROM (SELECT SUM(A.SALE_MONEY) AS YYE,    " +
            "                               0 MLE " +
            "                          FROM RCL_DAILYSALESREPORT A    " +
            "                         WHERE EXTRACT(MONTH FROM A.DATE_OF_STATISTICS) = :months " +
            "                           AND EXTRACT(YEAR FROM A.DATE_OF_STATISTICS) = :years" +
            "                           AND A.DEPT_ID = :deptId    " +
            "                        UNION ALL    " +
            "                        SELECT 0 AS YYE,    " +
            "                               SUM(A.AMT_PROFIT) MLE " +
            "                          FROM rpt_gross_profit A    " +
            "                         WHERE EXTRACT(MONTH FROM A.STATISTICS_DATE) = :months " +
            "                           AND EXTRACT(YEAR FROM A.STATISTICS_DATE) = :years    " +
            "                           AND A.INCOME_CATEGORIES != '预收'    " +
            "                           AND A.DEPT_ID = :deptId) T",nativeQuery = true)
    Map<String, Object> findAmt(@Param("months") String months, @Param("years") String years, @Param("deptId") String deptId);

    @Query(value = "SELECT * FROM GL_STORE_TASKS A WHERE EXTRACT(MONTH FROM A.YEAR_MONTH) = :months AND EXTRACT(YEAR FROM A.YEAR_MONTH) = :years",nativeQuery = true)
    List<StoreTasks> findAllByYearMonth(@Param("months") String months, @Param("years") String years);
}
